using System.Data;
using Chaotic.DataBase.DataAccess;
using Chaotic.DataBase.DBAccessLibrary.DBAccessLibrary;
using Chaotic.DataBase.DBAccessLibrary.Interface;
using Chaotic.DataBase.DBAccessLibrary.Model;

namespace Chaotic.DataBase.MSSQLAccess;

public class SqlGrammar : DBGrammar, IGrammar, ILocalGrammar
{ 
	public SqlGrammar(ConnectionModel? connect)
		: base(connect)
	{
	}

    public string? IsExistChTableSql
    {
        get
        {
            string? result = string.Empty;
            if (string.IsNullOrEmpty(_xml.ChDescription))
            {
                result = "select name from sysobjects where name='T_FIELD_DICTIONARY'";
            }
            return result;
        }
    }


    public override IEnumerable<string?> EditColumn(FieldEntity? field)
	{
		List<string?> list = new List<string?>();
		if (field.IsChange((FieldEntity o) => o.ColumnName))
		{
			list.Add($"EXEC SP_RENAME '{field.TableName}.{field.GetNoChangeValue((FieldEntity o) => o.ColumnName)}','{field.ColumnName}','COLUMN'");
		}
		if (field.IsChange((FieldEntity o) => o.DefaultValue))
		{
			string? sql = $"select b.name from syscolumns a,sysobjects b where a.id=object_id('{field.TableName}') and b.id=a.cdefault and a.name ='{field.Name}' and b.name like 'DF%' ";
			DataSet? dataSet = DBOperatorFactory.GetOperator.ExecSqlToDS(sql, _connect);
			foreach (DataRow row in dataSet.Tables[0].Rows)
			{
				string? item = $"alter table {field.TableName} drop constraint {row[0]} ";
				list.Add(item);
			}
			list.Add(GetTypeFilter(field, "ALTER TABLE [{0}] ALTER COLUMN {1} {2}{3}"));
			if (!string.IsNullOrEmpty(field.DefaultValue))
			{
				sql = string.Format("ALTER TABLE [{0}] ADD CONSTRAINT DF_{1}_{0} DEFAULT({2}) FOR {1}", field.TableName, field.Name, field.DefaultValue);
				list.Add(sql);
			}
		}
		else
		{
			list.Add(GetTypeFilter(field, "ALTER TABLE [{0}] ALTER COLUMN {1} {2}{3}"));
		}
		return list;
	}

	public override IEnumerable<string?> RemoveColumn(FieldEntity field)
	{
		List<string?> list = new List<string?>();
		if (field.IsForeignKey || field.IsPrimaryKey || !string.IsNullOrEmpty(field.DefaultValue))
		{
			string? sql = $"select b.name from syscolumns a,sysobjects b where a.id=object_id('{field.TableName}') and b.id=a.cdefault and a.name ='{field.Name}' and b.name like 'DF%' ";
			DataSet? dataSet = DBOperatorFactory.GetOperator.ExecSqlToDS(sql, _connect);
			foreach (DataRow row in dataSet.Tables[0].Rows)
			{
				string? item = $"alter table {field.TableName} drop constraint {row[0]} ";
				list.Add(item);
			}
		}
		list.Add($"ALTER TABLE [{field.TableName}] DROP COLUMN {field.TransName}");
		return list;
	}
 

	public string GetPageingSql(string sql, decimal index, decimal count)
	{
		string format = "SELECT tab.* FROM (SELECT {0} ROWNUMBER,t.* FROM (" + Environment.NewLine + "{1}" + Environment.NewLine + ") t) tab  WHERE tab.ROWNUMBER >= " + index + " and tab.ROWNUMBER < " + count;
		int length = sql.IndexOf("FROM", StringComparison.CurrentCultureIgnoreCase);
		string text = sql.Substring(0, length);
		if (text.IndexOf("count(") > 0 || text.IndexOf("sum(") > 0)
		{
			return sql;
		}
		if (sql.IndexOf("ORDER BY", StringComparison.CurrentCultureIgnoreCase) > 0)
		{
			sql = sql.Trim();
			if (sql.Substring(0, 6).ToLower() == "select" && !sql.Substring(6).Trim().StartsWith("TOP "))
			{
				sql = sql.Insert(6, " TOP " + count + " ");
			}
		}
		return string.Format(format, "ROW_NUMBER() OVER(ORDER BY GETDATE())", sql);
	}
 
	public DatabaseType GetConnectionStr(out string connectStr)
	{
		connectStr = "Data Source={3};Initial Catalog={0};User Id={1};Password={2};Trusted_Connection=True;Encrypt=false;";
		connectStr = string.Format(connectStr, _connect.DataBase, _connect.UserID, _connect.PassWord, _connect.Server);
		return DatabaseType.MSSQLServer;
	}
}
